from datetime import datetime

import pymysql

# 建立数据库连接
from settings import Config


def Mysql_connecter():
    conn = pymysql.connect(
        host=Config.HOST,
        user=Config.USER,
        password=Config.PASSWORD,
        database=Config.DATABASE,
        port=Config.PORT
    )
    return conn


# 根据用户名查询用户
def selectUserBYUserName(userName):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select * from users where userName='%s' and isActive='1';"
    try:
        realSql = sql % userName
        cur.execute(realSql)
        user = cur.fetchone()
        return user
    except Exception as e:
        print(e)
        return None


# 根据手机号查询用户
def selectUserByPhone(phone):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select * from users where phone='%s' and isActive='1';"
    try:
        realSql = sql % phone
        cur.execute(realSql)
        user = cur.fetchone()
        return user
    except Exception as e:
        print(e)
        return None


# 根据用户id查询用户
def selectUserById(uid):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select * from users where id='%s' and isActive='1';"
    try:
        realSql = sql % uid
        cur.execute(realSql)
        user = cur.fetchone()
        return user
    except Exception as e:
        print(e)
        return None


# 用户注册
def insertUsers(userName, password, sex, phone, email):
    conn = Mysql_connecter()
    nowTime = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    # 创建游标对象
    cur = conn.cursor()
    if sex == '1':
        icon = "man.png"
    else:
        icon = "woman.png"
    sql = "insert into users (userName,password,sex,phone,email,icon,isActive,insertTime) values ('%s','%s','%s','%s','%s','" + icon + "',true,'%s');"
    try:
        realSql = sql % (userName, password, sex, phone, email, nowTime)
        cur.execute(realSql)
        conn.commit()
    except Exception as e:
        print(e)
        return None


# 更新用户信息
def userUpdate(columu, value, id):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "update users set %s='%s' where id='%s';"
    try:
        realSql = sql % (columu, value, id)
        cur.execute(realSql)
        conn.commit()
    except Exception as e:
        print(e)
        return None
